set echo off
set define off
spool exp_source.log

declare
n_table number;
begin
select count(*) into n_table from user_tables where table_name='EBAO_SCRIPTS';
	if n_table >0
	then
		 execute immediate('truncate table ebao_scripts');
  else
  	 execute immediate('create table EBAO_SCRIPTS(SYN_ID NUMBER, SYN_CONTENT VARCHAR2(4000), SYN_TYPE NUMBER(2))');
  end if;
  
  select count(*) into n_table from user_sequences where sequence_name='S_EBAO_SCRIPTS';
  if n_table < 1
  then
  		execute immediate('create sequence S_EBAO_SCRIPTS minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 20');
  end if;
end;
/

declare
TYPE  type_1  IS REF CURSOR;
c1        type_1;
c2        type_1;
cr2	user_tab_columns%rowtype;
ls_table_name	varchar2(50);
ls_sql  varchar2(600);
ls_sql3 varchar2(600);
ls_sql2 varchar2(6000);
ls_sql4	varchar2(600);
lb_first boolean;
lb_first2 boolean;
ls_text varchar2(6000);
c3        type_1;
c4	  type_1;
ls_column_name varchar2(50);
ls_partition_type	varchar2(30);
ls_susbpart_type varchar2(30);
ln_part_count	number(4);
ls_partition_name varchar2(30);
ln_subpart_count number(4);
ls_high_value varchar2(100);
ls_subpart_name	varchar2(30);
ls_subpart_value varchar2(200);
ls_subpart_column varchar2(30);
ls_index_name	varchar2(30);
ls_locality varchar2(10);
ln_position number(4);

begin
	ls_sql := 'Select Distinct a.TABLE_NAME From user_tables a, user_objects b, user_lobs c Where a.table_name=b.object_name and b.object_type=''TABLE'' and a.table_name=c.table_name and a.table_name not like ''BIN$%'' and a.table_name not in(select name from user_part_key_columns where object_type=''TABLE'')'; 
	ls_sql2 := '';
	open c1 for ls_sql;
	loop
		fetch c1 into ls_table_name;
		EXIT WHEN c1%NOTFOUND;
		ls_sql2 := 'create table ' || ls_table_name || '(';
		ls_sql3 := 'Select * From user_tab_columns Where table_name = ''' || ls_table_name || ''' Order By column_id';
		open c2 for ls_sql3;
		lb_first := true;
		loop
			fetch c2 into cr2;
			EXIT WHEN c2%NOTFOUND;
			if lb_first then
				lb_first := false;
				ls_sql2 := ls_sql2||cr2.column_name||' ';
			else
				ls_sql2 := ls_sql2 || ',' || cr2.column_name||' ';
			end if;
	
			if cr2.data_type='NUMBER' 
			then				
				if cr2.data_precision is null 
				then					
					ls_sql2 := ls_sql2||cr2.data_type;				
				elsif cr2.data_scale = 0 
				then					
					ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_precision||')';				
				else					
					ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_precision||','||cr2.data_scale||')';				
				end if;			
			elsif cr2.data_type='VARCHAR2' or cr2.data_type='NVARCHAR2'
			then				
				ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_length||')';			
			elsif cr2.data_type='CHAR' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_length||')';      
			elsif cr2.data_type='RAW' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_length||')';			
			elsif cr2.data_type='DATE' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type;			
			elsif cr2.data_type='BLOB' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type;			
			elsif cr2.data_type='CLOB' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type;			
			else				
				ls_sql2 := ls_sql2||cr2.data_type;			
			end if;
			
			if cr2.data_default is not null 
			then				
				ls_sql2 := ls_sql2 || ' default ' || replace(replace(cr2.data_default,chr(10),''),chr(13),'');			
			end if;			
			if cr2.NULLABLE = 'N' 
			then				
				ls_sql2 := ls_sql2 || ' not null '  ;			
			end if;
			if length(ls_sql2) > 1500 
			then        		
				INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
				commit;					
				ls_sql2 := ' ';			
			end if;
		end loop;
		ls_sql2 := ls_sql2 || ');';
		INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
		commit;
		ls_sql2 := '';
	end loop;

	ls_sql := 'Select Name,text  From user_source Where Type=''TYPE'' and length(text)>1 and name not like ''SYS_PLSQL%'' Order By Name,line'; 
	ls_sql3 := '';
	lb_first := true;
	open c1 for ls_sql;
	loop
		fetch c1 into ls_table_name,ls_sql2;
		EXIT WHEN c1%NOTFOUND;
		if lb_first 
		then
			ls_text := 'create or replace ' || ls_sql2;
			ls_sql3 := ls_table_name;
			lb_first := false;
		else
			if ls_sql3 <> ls_table_name
			then
					ls_sql3 := ls_table_name;
					INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_text,1);
					INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,'/',1);
					commit;
					ls_text := 'create or replace ' || ls_sql2;
			else
				ls_text := ls_text || ls_sql2;
			end if;
		end if;
	 end loop;
	 if not lb_first
	 then
	 	INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_text,1);
	 	INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,'/',1);
		commit;
	 end if;
		
	--partition
	ls_sql := 'select c.name,c.column_name from user_part_key_columns c where c.object_type=''TABLE'' and c.name not like ''BIN$%''';
				
	ls_sql2 := '';
	open c1 for ls_sql;
	loop
		fetch c1 into ls_table_name,ls_column_name;
		EXIT WHEN c1%NOTFOUND;
		ls_sql2 := 'create table ' || ls_table_name || '(';
		ls_sql3 := 'Select * From user_tab_columns Where table_name = ''' || ls_table_name || ''' Order By column_id';
		open c2 for ls_sql3;
		lb_first := true;
		loop
			fetch c2 into cr2;
			EXIT WHEN c2%NOTFOUND;
			if lb_first then
				lb_first := false;
				ls_sql2 := ls_sql2||cr2.column_name||' ';
			else
				ls_sql2 := ls_sql2 || ',' || cr2.column_name||' ';
			end if;
	
			if cr2.data_type='NUMBER' 
			then				
				if cr2.data_precision is null 
				then					
					ls_sql2 := ls_sql2||cr2.data_type;				
				elsif cr2.data_scale = 0 
				then					
					ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_precision||')';				
				else					
					ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_precision||','||cr2.data_scale||')';				
				end if;			
			elsif cr2.data_type='VARCHAR2' or cr2.data_type='NVARCHAR2'
			then				
				ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_length||')';			
			elsif cr2.data_type='CHAR' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_length||')';      
			elsif cr2.data_type='RAW' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type||'('||cr2.data_length||')';			
			elsif cr2.data_type='DATE' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type;			
			elsif cr2.data_type='BLOB' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type;			
			elsif cr2.data_type='CLOB' 
			then				
				ls_sql2 := ls_sql2||cr2.data_type;			
			else				
				ls_sql2 := ls_sql2||cr2.data_type;			
			end if;
			
			if cr2.data_default is not null 
			then				
				ls_sql2 := ls_sql2 || ' default ' || replace(replace(cr2.data_default,chr(10),''),chr(13),'');			
			end if;			
			if cr2.NULLABLE = 'N' 
			then				
				ls_sql2 := ls_sql2 || ' not null '  ;			
			end if;
			if length(ls_sql2) > 1500 
			then        		
				INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
				commit;					
				ls_sql2 := ' ';			
			end if;
		end loop;
		ls_sql2 := ls_sql2 || ')';
		INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
		commit;
		ls_sql2 := ' ';
		------partition part
		ls_sql3 := 'Select a.partitioning_type,a.partition_count,a.subpartitioning_type,b.partition_name,b.subpartition_count,b.high_value ' ||
						'From user_part_tables a, user_tab_partitions b ' ||
						'Where a.table_name=b.table_name And a.table_name='''||ls_table_name||''' order by b.partition_position';
		open c3 for ls_sql3;
		lb_first := true;
		loop
			fetch c3 into ls_partition_type,ln_part_count,ls_susbpart_type,ls_partition_name,ln_subpart_count,ls_high_value;
			EXIT WHEN c3%NOTFOUND;
			if ls_partition_type='HASH' then
				ls_sql2 := ls_sql2 || ' partition by hash (' || ls_column_name || ') partitions ' || to_char(ln_part_count) || ';';
				INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
				commit;
				ls_sql2 := ' ';
				exit;
			else
				if lb_first then				
					lb_first := false;
					ls_sql2 := ls_sql2||' partition by ' || ls_partition_type ||' (' || ls_column_name || ') ';
					INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
					commit;
					ls_sql2 := ' ';
					if ln_subpart_count > 0 then
								--subpartition column
								ls_sql4 := 'Select e.column_name From user_subpart_key_columns e Where Name='''|| ls_table_name || ''''; 
							  open c4 for ls_sql4;
							  fetch c4 into ls_subpart_column;
							  close c4;
							  ls_sql2 := ls_sql2 || ' subpartition by ' || ls_susbpart_type || ' (' || ls_subpart_column || ')';
							  INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
								commit;
								ls_sql2 := ' ';
								
								if ls_susbpart_type = 'LIST' then
										--subpartition template
									  ls_sql4 := 'select subpartition_name,high_bound from User_Subpartition_Templates where table_name='''||ls_table_name || ''' order by subpartition_position';
									  open c4 for ls_sql4;
									  lb_first2 := true;
									  loop
									  	fetch c4 into ls_subpart_name,ls_subpart_value;
									  	EXIT WHEN c4%NOTFOUND;
									  	if lb_first2 then
									  			lb_first2 := false;
									  			ls_sql2 := ls_sql2 || 'subpartition template ( subpartition ' || ls_subpart_name || ' values (' || ls_subpart_value || ')';
									  	else
									  			ls_sql2 := ls_sql2 || ', subpartition ' || ls_subpart_name || ' values (' || ls_subpart_value || ')';
									  	end if;
									  	if length(ls_sql2) > 1500 
											then        		
													INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
													commit;					
													ls_sql2 := ' ';			
											end if;
										end loop;
										ls_sql2 := ls_sql2 || ')';
								else
										ls_sql2 := ' subpartitions ' || to_char(ln_subpart_count) ;
								end if; 
								INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
								commit;					
								ls_sql2 := ' ';
					end if;
					if (substr(ls_partition_name,1,3)<>'SYS' and ls_partition_type='LIST') then
						ls_sql2 := ' (partition ' || ls_partition_name || ' values (' || ls_high_value || ')';
					elsif (substr(ls_partition_name,1,3)<>'SYS' and ls_partition_type='RANGE') then
						ls_sql2 := ' (partition ' || ls_partition_name || ' values less than (' || ls_high_value || ')';
				  elsif (substr(ls_partition_name,1,3)='SYS' and ls_partition_type='RANGE') then
						ls_sql2 := ' (partition values less than (' || ls_high_value || ')';
					else
						ls_sql2 := ' (partition values (' || ls_high_value || ')';
					end if;
				else
					if (substr(ls_partition_name,1,3)<>'SYS' and ls_partition_type='LIST') then
						ls_sql2 := ', partition ' || ls_partition_name || ' values (' || ls_high_value || ')';
					elsif (substr(ls_partition_name,1,3)<>'SYS' and ls_partition_type='RANGE') then
						ls_sql2 := ', partition ' || ls_partition_name || ' values less than (' || ls_high_value || ')';
				  elsif (substr(ls_partition_name,1,3)='SYS' and ls_partition_type='RANGE') then
						ls_sql2 := ', partition values less than (' || ls_high_value || ')';
					else
						ls_sql2 := ', partition values (' || ls_high_value || ')';
					end if;
				end if;
			end if;
			INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
			commit;					
			ls_sql2 := ' ';
		end loop;
		ls_sql2 := ');';
		INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
		commit;
		ls_sql2 := ' ';
	end loop;	
	
  --partition index
	ls_sql := 'select c.name,b.table_name,b.def_subpartition_count,b.locality from user_part_key_columns c, user_part_indexes b where c.object_type=''INDEX'' and c.name not like ''BIN$%'' and c.name=b.index_name and c.column_position=1';
				
	ls_sql2 := '';
	open c1 for ls_sql;
	loop
		fetch c1 into ls_index_name,ls_table_name,ln_subpart_count,ls_locality;
		EXIT WHEN c1%NOTFOUND;
		ls_sql2 := 'create index ' || ls_index_name || ' on ' || ls_table_name || '(';
		
		------partition part
		ls_sql3 := 'Select column_name,column_position From user_ind_columns Where index_name='''||ls_index_name||''' order by column_position';
		lb_first := true;
		open c3 for ls_sql3;
		loop
			fetch c3 into ls_column_name,ln_position;
			EXIT WHEN c3%NOTFOUND;
			if lb_first
			then
				lb_first := false;
				ls_sql2 := ls_sql2 || ls_column_name;
			else
				ls_sql2 := ls_sql2 || ',' || ls_column_name;
			end if;
		end loop;
		ls_sql2 := ls_sql2 || ') ' || ls_locality || ';';
		INSERT INTO ebao_scripts (syn_id ,syn_content,syn_type) VALUES (s_ebao_scripts.nextval,ls_sql2,1);
		commit;					
		ls_sql2 := ' ';
	end loop;				
end;
/

SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 4000
SET PAGESIZE 0
SET SERVEROUTPUT OFF
SET ECHO OFF
SET VER OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
spool ../exp_dump_file/LOB_TYPE_DDL.sql
select syn_content from ebao_scripts where syn_type=1 order by syn_Id;
spool off

drop table ebao_scripts;
drop sequence S_EBAO_SCRIPTS;

quit
